namespace SubSonic
{
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Data;
    using System.Data.Common;

    public class ELib3DataProvider : SqlDataProvider
    {
        public override int ExecuteQuery(QueryCommand qry)
        {
            DbCommand sqlStringCommand;
            Database database = DatabaseFactory.CreateDatabase();
            if (qry.CommandType == CommandType.Text)
            {
                sqlStringCommand = database.GetSqlStringCommand(qry.CommandSql);
            }
            else
            {
                sqlStringCommand = database.GetStoredProcCommand(qry.CommandSql);
            }
            sqlStringCommand.CommandType = qry.CommandType;
            foreach (QueryParameter parameter in qry.Parameters)
            {
                database.AddInParameter(sqlStringCommand, parameter.ParameterName, parameter.DataType, parameter.ParameterValue);
            }
            return database.ExecuteNonQuery(sqlStringCommand);
        }

        public override object ExecuteScalar(QueryCommand qry)
        {
            DbCommand sqlStringCommand;
            Database database = DatabaseFactory.CreateDatabase();
            if (qry.CommandType == CommandType.Text)
            {
                sqlStringCommand = database.GetSqlStringCommand(qry.CommandSql);
            }
            else
            {
                sqlStringCommand = database.GetStoredProcCommand(qry.CommandSql);
            }
            sqlStringCommand.CommandType = qry.CommandType;
            foreach (QueryParameter parameter in qry.Parameters)
            {
                database.AddInParameter(sqlStringCommand, parameter.ParameterName, parameter.DataType, parameter.ParameterValue);
            }
            return database.ExecuteScalar(sqlStringCommand);
        }

        public override void ExecuteTransaction(QueryCommandCollection commands)
        {
            Database database = DatabaseFactory.CreateDatabase();
            if (commands.Count > 0)
            {
                DbCommand sqlStringCommand = null;
                using (DbConnection connection = database.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    foreach (QueryCommand command2 in commands)
                    {
                        if (command2.CommandType == CommandType.Text)
                        {
                            sqlStringCommand = database.GetSqlStringCommand(command2.CommandSql);
                        }
                        else
                        {
                            sqlStringCommand = database.GetStoredProcCommand(command2.CommandSql);
                        }
                        try
                        {
                            foreach (QueryParameter parameter in command2.Parameters)
                            {
                                database.AddInParameter(sqlStringCommand, parameter.ParameterName, parameter.DataType, parameter.ParameterValue);
                            }
                            database.ExecuteNonQuery(sqlStringCommand, transaction);
                            continue;
                        }
                        catch (DbException exception)
                        {
                            transaction.Rollback();
                            connection.Close();
                            sqlStringCommand.Dispose();
                            throw new Exception(exception.Message);
                        }
                    }
                    transaction.Commit();
                    connection.Close();
                    if (sqlStringCommand != null)
                    {
                        sqlStringCommand.Dispose();
                    }
                    return;
                }
            }
            throw new Exception("No commands present");
        }

        public override IDbCommand GetCommand(QueryCommand qry)
        {
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand sqlStringCommand = database.GetSqlStringCommand(qry.CommandSql);
            foreach (QueryParameter parameter in qry.Parameters)
            {
                database.AddInParameter(sqlStringCommand, parameter.ParameterName, parameter.DataType, parameter.ParameterValue);
            }
            return sqlStringCommand;
        }

        public override T GetDataSet<T>(QueryCommand qry) //where T: DataSet, new()
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dataSetCommand = GetDataSetCommand(db, qry);
            if (typeof(T) == typeof(DataSet))
            {
                return (T) db.ExecuteDataSet(dataSetCommand);
            }
            T ds = Activator.CreateInstance<T>();
            db.LoadDataSet(dataSetCommand, ds, DataProvider.GetTableNames(ds));
            return ds;
        }

        private static DbCommand GetDataSetCommand(Database db, QueryCommand qry)
        {
            DbCommand sqlStringCommand;
            if (qry.CommandType == CommandType.Text)
            {
                sqlStringCommand = db.GetSqlStringCommand(qry.CommandSql);
            }
            else
            {
                sqlStringCommand = db.GetStoredProcCommand(qry.CommandSql);
            }
            foreach (QueryParameter parameter in qry.Parameters)
            {
                db.AddInParameter(sqlStringCommand, parameter.ParameterName, parameter.DataType, parameter.ParameterValue);
            }
            return sqlStringCommand;
        }

        public override DbCommand GetDbCommand(QueryCommand qry)
        {
            DbCommand storedProcCommand;
            Database database = DatabaseFactory.CreateDatabase();
            if (qry.CommandType == CommandType.StoredProcedure)
            {
                storedProcCommand = database.GetStoredProcCommand(qry.CommandSql);
            }
            else
            {
                storedProcCommand = database.GetSqlStringCommand(qry.CommandSql);
            }
            foreach (QueryParameter parameter in qry.Parameters)
            {
                database.AddInParameter(storedProcCommand, parameter.ParameterName, parameter.DataType, parameter.ParameterValue);
            }
            return storedProcCommand;
        }

        public override IDataReader GetReader(QueryCommand qry)
        {
            DbCommand sqlStringCommand;
            Database database = DatabaseFactory.CreateDatabase();
            if (qry.CommandType == CommandType.Text)
            {
                sqlStringCommand = database.GetSqlStringCommand(qry.CommandSql);
            }
            else
            {
                sqlStringCommand = database.GetStoredProcCommand(qry.CommandSql);
            }
            foreach (QueryParameter parameter in qry.Parameters)
            {
                database.AddInParameter(sqlStringCommand, parameter.ParameterName, parameter.DataType, parameter.ParameterValue);
            }
            return database.ExecuteReader(sqlStringCommand);
        }
    }
}

